﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DbSync.Common
{
    /// <summary>
    /// Sql Helper  date:202101
    /// </summary>
    public sealed class DBHelper
    {
        private readonly static object olock = new object();
        private readonly static Hashtable CachedFactories = Hashtable.Synchronized(new Hashtable());
        private readonly DbProviderFactory _factory = null;
        private readonly string _connectionString = null;
        public List<DbConnection> ConnPool = new List<DbConnection>();
        private Dictionary<string, string> _dbTypeMap = new Dictionary<string, string>() {
            { "MySql", "MySql.Data.MySqlClient" },
             { "Access", "System.Data.OleDb" },
              { "Oracle", "System.Data.OracleClient" },
               { "SQLite", "System.Data.SQLite" },
               { "MsSql", "System.Data.SqlClient" }
        };
        /// <summary>
        /// DBHelper
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <param name="ProviderName"></param>
        public DBHelper(string ConnectionString, DBType ProviderName = DBType.MySql)
        {
            _factory = GetFactory(_dbTypeMap[DBType.MySql.ToString()]);
            _connectionString = ConnectionString;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbConfigName">database config name</param>
        private DBHelper(string dbConfigName)
        {
            var setting = ConfigurationManager.ConnectionStrings[dbConfigName];

            if (setting == null) throw new ArgumentException("dbConfigName");

            _factory = GetFactory(setting.ProviderName);
            _connectionString = setting.ConnectionString;
        }
        public bool CanConnect()
        {
            DbConnection currentConnection = null;
            try
            {
                currentConnection = this.CreateConnection();
                return true;
            }
            catch (Exception ex) {
                return false;
            }
            finally
            {
                if (currentConnection != null)
                {
                    currentConnection.Close();
                }
            }                       
        }

        ~DBHelper()
        {
            Close();
        }

        #region private properties/methods
        private DbConnection CreateConnection()
        {
            lock (olock)
            {
                DbConnection currentConnection = null;
                foreach (var conn in ConnPool)
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                        currentConnection = conn;
                        break;
                    }
                }
                if (currentConnection == null)
                {
                    try
                    {
                        currentConnection = _factory.CreateConnection();
                        currentConnection.ConnectionString = _connectionString;
                        currentConnection.Open();
                        ConnPool.Add(currentConnection);

                    }
                    catch(Exception ex)
                    {
                        throw ex;
                        //return null;
                    }

                }
                return currentConnection;
            }               
        }

        private static DbProviderFactory GetFactory(string providerName)
        {
            DbProviderFactory factory = CachedFactories[providerName] as DbProviderFactory;
            if (factory == null)
            {
                lock (CachedFactories.SyncRoot)
                {
                    factory = DbProviderFactories.GetFactory(providerName);
                    CachedFactories.Add(providerName, factory);
                }
            }

            return factory;
        }
        #endregion

        #region public methods
      
        /// <summary>
        /// 
        /// </summary>
        public void Close()
        {
            foreach (var conn in ConnPool)
            {
                conn.Close();
                conn.Dispose();
            }          
        }

        /// <summary>
        /// 
        /// </summary>
        public bool  UseTransaction(Func<DbTransaction , bool> func)
        {
            var transaction = CreateConnection().BeginTransaction();
            try
            {
                var result = func.Invoke(transaction);
                transaction.Commit();
                return result;
            }
            catch(Exception ex)
            {
                transaction.Rollback();
                return false;
            }
            finally
            {
                transaction.Dispose();
            }                                         
        }


        #endregion

        #region CreateCommand
        /// <summary>
        /// create new sql command
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParams"></param>
        /// <param name="Connection"></param>
        /// <returns></returns>
        private DbCommand CreateCommand(CommandType commandType, string commandText,
            IEnumerable<DbParameter> commandParams, DbConnection Connection,bool useTransaction=false)
        {
            if (Connection == null) throw new ArgumentNullException("_connection");

            if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");

            DbCommand command = Connection.CreateCommand();
            if (useTransaction)
            {
                command.Transaction = Connection.BeginTransaction();
            }            
            command.CommandText = commandText;
          
            command.CommandType = commandType;

            if (commandParams != null)
            {
                foreach (DbParameter p in commandParams)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }

            return command;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, null);
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, IEnumerable<DbParameter> cmdParams)
        {
            var Connection = CreateConnection();
            try
            {
                DbCommand cmd = CreateCommand(commandType, commandText, cmdParams, Connection,true);
                int ret = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Transaction.Commit();
                return ret;
            }
            catch(Exception ex)
            {
                return -1;
            }
            finally
            {
                Connection.Close();
            }
                      
        }
        #endregion
        public DataTable ExecuteDataTable(string commandText)
        {
            return ExecuteDataset(CommandType.Text, commandText, null).Tables[0];
        }
        public DataTable ExecuteDataTable(string commandText, IEnumerable<DbParameter> cmdParams)
        {
            return ExecuteDataset(CommandType.Text, commandText, cmdParams).Tables[0];
        }
        #region ExecuteDataset
        /// <summary>
        /// ExecuteDataset
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(string commandText)
        {
            return ExecuteDataset(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// ExecuteDataset
        /// </summary>
        /// <param name="spName">store procedure name</param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(string spName, IEnumerable<DbParameter> cmdParams)
        {
            return ExecuteDataset(CommandType.StoredProcedure, spName, cmdParams);
        }

        /// <summary>
        /// ExecuteDataset
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return ExecuteDataset(commandType, commandText, null);
        }

        /// <summary>
        /// ExecuteDataset
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(CommandType commandType, string commandText, IEnumerable<DbParameter> cmdParams)
        {
            var Connection = CreateConnection();           
            try
            {
                DbCommand cmd = CreateCommand(commandType, commandText, cmdParams, Connection);
                using (DbDataAdapter adapter = _factory.CreateDataAdapter())
                {
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    cmd.Parameters.Clear();                
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Connection.Close();
            }
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public List<Dictionary<string, string>> ExecuteReader(string commandText, Func<DbDataReader, List<Dictionary<string, string>>> acs)
        {
            return ExecuteReader(CommandType.Text, commandText, null,acs);
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public List<Dictionary<string, string>> ExecuteReader(CommandType commandType, string commandText, Func<DbDataReader, List<Dictionary<string, string>>> acs)
        {
            return ExecuteReader(commandType, commandText, null,acs);
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public List<Dictionary<string, string>> ExecuteReader(CommandType commandType, string commandText, IEnumerable<DbParameter> cmdParams,Func<DbDataReader, List<Dictionary<string, string>>> Acs)
        {
            List<Dictionary<string, string>> datas = null;
            var Connection = CreateConnection();
            try
            {
                DbCommand cmd = CreateCommand(commandType, commandText, cmdParams, Connection);
                DbDataReader reader = cmd.ExecuteReader(); ;
                datas= Acs.Invoke(reader);
                reader.Close();
                return datas;
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                Connection.Close();
            }                      
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(CommandType.Text, commandText, null);
        }
        public T ExecuteScalar<T>(string commandText)
        {
            var value= ExecuteScalar(CommandType.Text, commandText, null);
            try
            {
                return (T)value;
            }
            catch (Exception ex)
            {
                throw ex;
                //return default(T);
            }             
        }
        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(commandType, commandText, null);
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType commandType, string commandText, IEnumerable<DbParameter> cmdParams)
        {          
            var Connection = CreateConnection();
            DbCommand cmd = CreateCommand(commandType, commandText, cmdParams, Connection);
            try
            {
                
                object ret = cmd.ExecuteScalar();

                cmd.Parameters.Clear();
             
                return ret;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Transaction.Dispose();
                cmd.Dispose();
                Connection.Close();
            }

        }
        #endregion

        #region create parameter
        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public DbParameter MakeInParam(string paramName, DbType dbType, object paramValue)
        {
            return MakeParam(paramName, dbType, 0, ParameterDirection.Input, paramValue);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public DbParameter MakeInParam(string paramName, DbType dbType, int size, object paramValue)
        {
            return MakeParam(paramName, dbType, size, ParameterDirection.Input, paramValue);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public DbParameter MakeOutParam(string paramName, DbType dbType)
        {
            return MakeParam(paramName, dbType, 0, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public DbParameter MakeOutParam(string paramName, DbType dbType, int size)
        {
            return MakeParam(paramName, dbType, size, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="direction"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public DbParameter MakeParam(string paramName, DbType dbType, Int32 size, ParameterDirection direction, object paramValue)
        {
            DbParameter param = _factory.CreateParameter();
            param.ParameterName = paramName;
            param.DbType = dbType;
            param.Size = size;
            param.Direction = direction;

            if (!(direction == ParameterDirection.Output && paramValue == null))
            {
                param.Value = paramValue;
            }

            return param;
        }
        #endregion
    }
    public enum DBType
    {
        // "MySql.Data.MySqlClient",
        // System.Data.OleDb  System.Data.OracleClient  System.Data.SQLite System.Data.SqlClient
        MySql,
        Oracle,
        SQLite,
        Access,
        MsSql
    }

}
